package com.example.spring.multi.basic.dingdang;

import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.example.spring.multi.basic.dingdang.model.CityInfoModel;
import com.example.spring.multi.basic.dingdang.model.ResultInfoModel;
import com.example.spring.multi.basic.dingdang.model.TotalResultModel;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;

import java.io.File;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @program: knowledge-pooling
 * @ClassName DDExcelDealStartUtil
 * @description: 叮当数据处理起点工具类
 * @author: tomato
 * @create: 2024-07-17 16:18
 * @Version 1.0
 **/
@Slf4j
public class DDExcelDealStartUtil {
    private static final String DD_EXCEL_FILE_PATH = "/Users/fanxiaodiu/Desktop/File/dingdang/dd/"; // 叮当数据Excel文件路径


    public static List<TotalResultModel> PROCESS_RESULT_DATA = new ArrayList<>();
    //首先读取总表Excel文件，获取对应城市的文件名称
    public static void buildSignInDateModels(String cityName) {
        //总表匹配库存表，门店配送表，计算库存量和配送量
        Map<String, List<ResultInfoModel>> groupedByCity = getSummaryTableInfoListMap();
        if (groupedByCity == null) {
            return;
        }
        File file = new File(DD_EXCEL_FILE_PATH + "cityInfo.xlsx");
        // 读取总表Excel文件
        //如果文件不是excel文件，直接返回
        if (file.getName().indexOf("xls") == -1 && file.getName().indexOf("xlsx") == -1) {
            log.info("{}不是excel文件", file.getName());
            return;
        }
        ExcelReader reader = ExcelUtil.getReader(file);
        // 读取表头信息,并且将表头信息转换成对应的实体类
        reader.addHeaderAlias("连锁仓库表", "stockExcelName");
        reader.addHeaderAlias("城市", "cityName");
        reader.addHeaderAlias("门店配送表", "distributionExcelName");
        List<CityInfoModel> dataList = reader.readAll(CityInfoModel.class);
        // 关闭reader
        reader.close();
        if (CollectionUtils.isEmpty(dataList)) {
            return;
        }
        // 遍历所有城市信息，获取对应城市的门店配送表Excel文件
        for (CityInfoModel cityInfoModel : dataList) {
            String city = cityInfoModel.getCityName();
            if (!city.equals(cityName)) {
                continue;
            }
            if (!groupedByCity.containsKey(city)) {
                continue;
            }
            String stockExcelName = cityInfoModel.getStockExcelName();
            String distributionExcelName = cityInfoModel.getDistributionExcelName();
            if (distributionExcelName == null || distributionExcelName.isEmpty()
                    || stockExcelName == null || stockExcelName.isEmpty()) {
                continue;
            }
            File distributionFile = new File(DD_EXCEL_FILE_PATH + distributionExcelName + ".xlsx");
            File stockFile = new File(DD_EXCEL_FILE_PATH + stockExcelName + ".xlsx");

            if (!distributionFile.exists() || !stockFile.exists()) {
                continue;
            }
            // 读取库存表Excel文件，获取对应数据
            Map<String, Integer> dealStockExcelMap = StockExcelDealUtil.dealStockExcel(cityName,stockFile);
            // 读取门店配送表Excel文件，获取对应数据
            Map<String, Integer> dealDeliveryDetailsMap = DeliveryDetailsExcelUtil.dealDeliveryDetailsExcel(distributionFile);

            List<ResultInfoModel> resultInfoModels = groupedByCity.get(city);
            //校验
            if (CollectionUtils.isEmpty(resultInfoModels)) {
                continue;
            }
            //匹配库存量
            resultInfoModels.forEach(resultInfoModel -> {
                String productCode = resultInfoModel.getProductCode();
                if (dealStockExcelMap.containsKey(productCode)) {
                    int quantity = dealStockExcelMap.get(productCode);
                    resultInfoModel.setInventory(quantity);
                } else {
                    resultInfoModel.setInventory(0);
                }
            });
            //匹配订单量
            resultInfoModels.forEach(resultInfoModel -> {
                String productCodeMerge = resultInfoModel.getProductCodeMerge();
                if (dealDeliveryDetailsMap.containsKey(productCodeMerge)) {
                    int quantity = dealDeliveryDetailsMap.get(productCodeMerge);
                    resultInfoModel.setOrderNum(quantity);
                } else {
                    resultInfoModel.setOrderNum(0);
                }
            });
            int total = resultInfoModels.size();
            resultInfoModels.removeIf(entity -> Objects.isNull(entity.getOrderNum()) || entity.getInventory() <= 3);
            int dealNum = resultInfoModels.size();
            //筛选出订单量为0或者为null的商品
            resultInfoModels.removeIf(entity -> entity.getOrderNum() != 0);
            int secondDealNum = resultInfoModels.size();
            System.out.println(city + " 共有" + total + "个商品，其中处理了" + dealNum + "个商品，最终结果有" + resultInfoModels.size() + "个商品");
            //写入excel文件
            TotalResultModel totalResultModel = new TotalResultModel();
            totalResultModel.setCityName(city);
            totalResultModel.setTotal(total);
            totalResultModel.setDealNum(dealNum);
            totalResultModel.setSecondDealNum(secondDealNum);

            PROCESS_RESULT_DATA.add(totalResultModel);
            writerResultToExcel();
        }
    }

    /**
     * 将处理后的数据写入到Excel文件中
     */
    private static void writerResultToExcel() {
        String fileName = "城市处理结果.xlsx";
        File writeFile = new File(DD_EXCEL_FILE_PATH + fileName);
        ExcelWriter writer = ExcelUtil.getWriter();
        writer.addHeaderAlias("cityName", "城市名称");
        writer.addHeaderAlias("total", "总数据量");
        writer.addHeaderAlias("dealNum", "一次匹配数据量");
        writer.addHeaderAlias("secondDealNum", "二次匹配数据量");
        writer.write(PROCESS_RESULT_DATA);
        // 根据内容自动设置列宽
        writer.autoSizeColumnAll();
        // 设置列宽为自动适应列宽的基础上再加10
        writer.setColumnWidth(-1, 10);

        writer.flush(writeFile);
        writer.close();
    }

    /**
     * 读取总表信息，获取对应城市的文件名称
     * @return 城市信息Map
     */
    private static Map<String, List<ResultInfoModel>> getSummaryTableInfoListMap() {
        File resultFile = new File(DD_EXCEL_FILE_PATH + "总表信息" + ".xlsx");
        if (!resultFile.exists()) {
            log.info("总表信息.xlsx文件不存在");
            return null;
        }
        ExcelReader reader1 = ExcelUtil.getReader(resultFile);
        // 读取表头信息,并且将表头信息转换成对应的实体类
        reader1.addHeaderAlias("城市", "city");
        reader1.addHeaderAlias("门店名称", "shopName");
        reader1.addHeaderAlias("英克商品编码", "productCode");
        List<ResultInfoModel> resultInfoModels = reader1.readAll(ResultInfoModel.class);
        // 关闭reader
        reader1.close();
        if (CollectionUtils.isEmpty(resultInfoModels)) {
            return null;
        }
        resultInfoModels = resultInfoModels.stream()
                .peek(ResultInfoModel::extractAndConcatToC) // 使用peek进行中间操作，不改变流的内容，只执行操作
                .collect(Collectors.toList()); // 收集回List
        // 将特定城市名映射到目标组名
        Map<String, String> cityToGroupNameMapping = new HashMap<>();
        cityToGroupNameMapping.put("佛山市", "广州市");
        cityToGroupNameMapping.put("中山市", "广州市");
        cityToGroupNameMapping.put("东莞市", "深圳市"); // 东莞市的数据归到深圳市组

        Map<String, List<ResultInfoModel>> groupedEntities = new HashMap<>();

        // 遍历实体集合进行分组
        for (ResultInfoModel entity : resultInfoModels) {
            String city = entity.getCity();
            String groupName = cityToGroupNameMapping.getOrDefault(city, city); // 如果不在映射中，则使用原始城市名作为组名
            // 如果该组还不存在，则创建一个新的列表来存储实体
            groupedEntities.putIfAbsent(groupName, new ArrayList<>());
            // 将实体添加到对应的组中
            groupedEntities.get(groupName).add(entity);
        }
        return groupedEntities;
    }
}
